package com.feriaderemates.dao.listing;

import com.feriaderemates.domain.listing.Listing;
import com.feriaderemates.domain.listing.ListingQuestion;
import com.feriaderemates.domain.listing.ListingShippingCarrier;
import com.feriaderemates.domain.listing.ListingStatus;
import com.feriaderemates.domain.listing.WatchedListing;
import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.List;
import org.springframework.jdbc.core.PreparedStatementCreator;
import org.springframework.jdbc.core.support.JdbcDaoSupport;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;

public class ListingDao extends JdbcDaoSupport implements ListingDaoInterface {

	
	// Queries
	private static String ADD_PARENT_LISTING ="INSERT INTO portal.listing (description,creation_date,start_date,end_date," +
			" username,warranty,listing_status,sale_type,listing_type,state_id,title,sub_title) " +
			" VALUES (?,NOW(),?,?,?,?,?,?,?,?,?,?)";
	private static String UPDATE_PARENT_LISTING ="UPDATE portal.listing  SET description =?," +
			" warranty = ?,state_id =?,title = ?,sub_title = ? WHERE listing_id = ? " ;
			
	private static String GET_ACTIVE_LISTING_BY_USERID = "select \"user\".username, listing.* from portal.\"user\",portal.listing "
                + " where \"user\".username = listing.username and \"user\".user_id = ? and listing.listing_status = ?";
        private static String ADD_LISTING_QUESTION = "INSERT INTO portal.listing_question (question,listing_id,username,date) VALUES (?,?,?,NOW())";
        private static String QUESTIONS_BY_LISTING_ID = "SELECT * FROM portal.listing_question WHERE listing_id = ?"; 
	private static String UPDATE_LIST_STATUS = "UPDATE portal.listing set listing_status = ? where listing_id = ?";
	
	
	
	private static String ADD_SHIPPING_CARRIER = "INSERT INTO portal.listing_shipping_carrier (shipping_carrier,listing_id,price) VALUES (?,?,?)";
	private static String ADD_USER_WATCH = "INSERT INTO portal.user_watch_list (user_id,listing_id) VALUES (?,?)";
	private static String DELETE_USER_WATCH = "DELETE FROM portal.user_watch_list WHERE listing_id = ? ";
	
	
	@Override
	public long addParentListing(final Listing listing) {
		KeyHolder keyHolder = new GeneratedKeyHolder();
        getJdbcTemplate().update(new PreparedStatementCreator() {
            @Override
            public PreparedStatement createPreparedStatement(Connection connection) throws SQLException {
                PreparedStatement ps = connection.prepareStatement(ADD_PARENT_LISTING, Statement.RETURN_GENERATED_KEYS);
                ps.setString(1, listing.getDescription());
                ps.setDate(2, (Date) listing.getStartDate());
                ps.setDate(3, (Date) listing.getEndDate());
                ps.setString(4, listing.getUsername());
                ps.setString(5, listing.getWarranty());
                ps.setString(6, listing.getListingStatus().name());
                ps.setString(7, listing.getSaleType().name());
                ps.setString(8, listing.getListingType().name());
                ps.setInt(9, listing.getState().getValue());
                ps.setString(10, listing.getTitle());
                ps.setString(11, listing.getSubTitle());
                return ps;
            }
        }, keyHolder);
        Object key = keyHolder.getKeys().get("listing_id");
		return (Long)key;
	}

	@Override
	public void updateParentListing(final Listing listing) {
		getJdbcTemplate().update(new PreparedStatementCreator() {
            @Override
            public PreparedStatement createPreparedStatement(Connection connection) throws SQLException {
                PreparedStatement ps = connection.prepareStatement(UPDATE_PARENT_LISTING);
                ps.setString(1, listing.getDescription());
                ps.setString(2, listing.getWarranty());
               
                ps.setInt(3, listing.getState().getValue());
                ps.setString(4, listing.getTitle());
                ps.setString(5, listing.getSubTitle());
                ps.setLong(6, listing.getListingId());
                return ps;
            }
        });

	}

	@Override
	public long addListingQuestion(final ListingQuestion listing) {
		KeyHolder keyHolder = new GeneratedKeyHolder();
        getJdbcTemplate().update(new PreparedStatementCreator() {
            @Override
            public PreparedStatement createPreparedStatement(Connection connection) throws SQLException {
                PreparedStatement ps = connection.prepareStatement(ADD_LISTING_QUESTION, Statement.RETURN_GENERATED_KEYS);
                ps.setString(1, listing.getQuestion());
                ps.setLong(2, listing.getListingId());
                ps.setString(3, listing.getQuestionerUsername());
                return ps;
            }
        }, keyHolder);
        Object key = keyHolder.getKeys().get("listing_question_id");
		return (Long)key;
	}

	@Override
	public List<ListingQuestion> getQuestionsByListingId(long listingId) {
		return getJdbcTemplate().query(QUESTIONS_BY_LISTING_ID,new Object[]{listingId}, new ListingQuestionRowMapper());
	}

	@Override
	public void updateListingShippingCarriers(
			List<ListingShippingCarrier> carriers) {
		// TODO Auto-generated method stub

	}

	@Override
	public void addListingShippingCarriers(List<ListingShippingCarrier> carriers) {
		if(!carriers.isEmpty()){
			for(final ListingShippingCarrier shippingCarrier : carriers){
				getJdbcTemplate().update(new PreparedStatementCreator() {
		            @Override
		            public PreparedStatement createPreparedStatement(Connection connection) throws SQLException {
		                PreparedStatement ps = connection.prepareStatement(ADD_SHIPPING_CARRIER);
		                ps.setString(1, shippingCarrier.getShippingCarrier().name());
		                ps.setLong(2, shippingCarrier.getListingId());
		                ps.setBigDecimal(3, shippingCarrier.getPrice());
		                return ps;
		            }
		        });
			}
		}

	}

	@Override
	public void addListingToWatch(final long listingId, final int userId) {
		getJdbcTemplate().update(new PreparedStatementCreator() {
            @Override
            public PreparedStatement createPreparedStatement(Connection connection) throws SQLException {
                PreparedStatement ps = connection.prepareStatement(ADD_USER_WATCH);
                ps.setInt(1, userId);
                ps.setLong(2, listingId);
                return ps;
            }
        });

	}

	@Override
	public List<WatchedListing> getUserWatchedListing(int userId) {
		// TODO Auto-generated method stub
		return null;
	}

	@Override
	public void deleteUserWatchedListing(final int listingId) {
		getJdbcTemplate().update(new PreparedStatementCreator() {
            @Override
            public PreparedStatement createPreparedStatement(Connection connection) throws SQLException {
                PreparedStatement ps = connection.prepareStatement(DELETE_USER_WATCH);
                ps.setInt(1, listingId);
                return ps;
            }
        });

	}

	@Override
	public List<Listing> getActiveListingsSummaryByUserId(int userId) {
            return getJdbcTemplate().query(GET_ACTIVE_LISTING_BY_USERID, new Object[]{userId,"ACTIVE"}, new ListingRowMapper());
	}

	@Override
	public void sumOneViewToListing(long listingId) {
		

	}

	@Override
	public void updateListingStatus(final long listingId, final ListingStatus status) {
            getJdbcTemplate().update(new PreparedStatementCreator() {
                @Override
                public PreparedStatement createPreparedStatement(Connection connection) throws SQLException {
                   PreparedStatement ps = connection.prepareStatement(UPDATE_LIST_STATUS);
                   ps.setString(1, status.name());
                   ps.setLong(2, listingId);
                   return ps;
                }
            });
       }

}
